<?php

class mdlCursos extends Zend_Db_Table_Abstract
{

    protected $_schema = 'moodle';
    protected $_name = 'mdl_course';
    protected $_dependentTable = array('Fabrica','FabricaCiclos','mdlContext');

    public function getCurso($id)
    {
        $id = (int)$id;
        $row = $this->fetchRow('id = ' . $id);
        if (!$row) {
            throw new Exception("mdl_course não localizado: $id");
        }
        return $row;
    }

    public function getCursosByCategoria($categoria_id)
    {
        $this->getAdapter()->setFetchMode(zend_db::FETCH_OBJ);
        $select = $this->getAdapter()->select()
        ->from(array('c' => 'moodle.mdl_course'),array('id','fullname'))
        ->where('c.category=?',$categoria_id)
        ->order('fullname');
        $stmt = $select->query();
        $result = $stmt->fetchAll();
        return $result;
    }
    /*
     *  lista os integrantes do curso conforme o roleid especificado
     */
    public function ListaAtoresByRoleid($course_id=null, $role_id=null) 
    {
        $this->getAdapter()->setFetchMode(zend_db::FETCH_OBJ);
        $select = $this->getAdapter()->select()
        ->from(array('ra' => 'moodle.mdl_role_assignments'),        array('roleid'=>'id'))
        ->join(array('u' =>  'moodle.mdl_user'),'u.id=ra.userid',   array('userid'=>'id','cargo'=>'cargo','nome'=>"concat(firstname,' ',lastname)"))
        ->join(array('r' =>  'moodle.mdl_role'),'r.id=ra.roleid',   array('perfil'=>'name'))
        ->join(array('ct' => 'moodle.mdl_context'),'ct.id=ra.contextid and ct.contextlevel=50',array(null))
        ->join(array('c' =>  'moodle.mdl_course'),'c.id=ct.instanceid',array('courseid'=>'id','curso'=>'shortname'))
        ->order('roleid','nome');
        
        if($course_id){
            $select->where('ct.instanceid=?',$course_id);
        }
        
        if(!$role_id==null){
            $select->where('ra.roleid=?',$role_id);
        }
        
        $stmt = $select->query();
        $result = $stmt->fetchAll();
        return $result;
    }
    
    /* 
     * lista todos os perfis de um determinado curso
     */
    public function ListaPerfisByCurso($course_id)  
    {
        $this->getAdapter()->setFetchMode(zend_db::FETCH_OBJ);
        $select = $this->getAdapter()->select()
        ->distinct()
        ->from(array('rs' => 'moodle.mdl_role_assignments'),array(null))
        ->join(array('r' => 'moodle.mdl_role'),'r.id=rs.roleid',array('id','role'=>'name'))
        ->join(array('u' => 'moodle.mdl_user'),'u.id=rs.userid',array(null))
        ->join(array('e' => 'moodle.mdl_context'),'rs.contextid=e.id',array(null))
        ->where('e.contextlevel=50')
        ->where('e.instanceid=?',$course_id)
        ->order("r.id desc");
        $stmt = $select->query();
//        $sql=$select->__toString();
        $result = $stmt->fetchAll();
        return $result;
    }
    
    public function ListaPerfisByUser($user_id)  
    {
        $this->getAdapter()->setFetchMode(zend_db::FETCH_ASSOC);
        $select = $this->getAdapter()->select()
        ->distinct()
        ->from(array('rs' => 'moodle.mdl_role_assignments'),array(null))
        ->join(array('r' => 'moodle.mdl_role'),'r.id=rs.roleid',array('id','role'=>'name'))
        ->join(array('u' => 'moodle.mdl_user'),'u.id=rs.userid',array(null))
        ->join(array('e' => 'moodle.mdl_context'),'rs.contextid=e.id',array(null))
        ->where('e.contextlevel=50')
        ->where('rs.userid=?',$user_id)
        ->order("rs.roleid");
        $stmt = $select->query();
//        $sql=$select->__toString();
        $result = $stmt->fetchAll();
        return $result;
    }
    
    public function ListaCursosByUser($user_id,$order=null)  
    {
        $this->getAdapter()->setFetchMode(zend_db::FETCH_ASSOC);
        $select = $this->getAdapter()->select()
        ->from(array('ra' => 'moodle.mdl_role_assignments'),        array('roleid'=>'id'))
        ->join(array('u' =>  'moodle.mdl_user'),'u.id=ra.userid',   array('userid'=>'id','cargo'=>'cargo','nome'=>"concat(firstname,' ',lastname)"))
        ->join(array('r' =>  'moodle.mdl_role'),'r.id=ra.roleid',   array('perfil'=>'name'))
        ->join(array('ct' => 'moodle.mdl_context'),'ct.id=ra.contextid and ct.contextlevel=50',array(null))
        ->join(array('c' =>  'moodle.mdl_course'),'c.id=ct.instanceid',array('inicio'=>'c.startdate','termino'=>'c.enddate','courseid'=>'id','curso'=>'fullname'))
        ->where('u.id = ?',$user_id)
        ->where('c.metacourse = 0')
        ->where('c.showgrades = 1');
        
        switch ($order) {
        case 1:
            $select->order(array('perfil','c.shortname'));
        break;
        case 2:
            $select->order(array('perfil','c.startdate'));
        break;
        case 3:
            $select->order(array('perfil','c.enddate'));
        break;
        default:
            $select->order(array('perfil','c.startdate desc','c.shortname'));
        break;
        }
                
        $stmt = $select->query();
        //$sql=$select->__toString();
        $result = $stmt->fetchAll();
        return $result;
    }

    public function ListaNotas($user_id=null, $course_id=null)  
    {
        $this->getAdapter()->setFetchMode(zend_db::FETCH_ASSOC);
        $select = $this->getAdapter()->select()
        ->from(array('gg' => 'moodle.mdl_grade_grades'), array('userid' => 'gg.userid', 'nota' => 'gg.finalgrade'))
        ->join(array('gi' => 'moodle.mdl_grade_items'),'gg.itemid = gi.id', array('courseid'=>'courseid','maxima'=>'gi.grademax', 'corte'=>'gi.gradepass'))
        ->join(array('gc' => 'moodle.mdl_grade_categories'),'gi.iteminstance = gc.id', array('disciplina' => 'gc.fullname'))
        ->join(array('c'  => 'moodle.mdl_course'),'gi.courseid = c.id', array('curso' => 'c.fullname'))
        ->join(array('u'  => 'moodle.mdl_user'),'gg.userid = u.id', array('participante'=>"CONCAT(u.firstname,' ',u.lastname)"));
        
        if($course_id){$select->where('gi.courseid = ?',$course_id);}
        if($user_id){$select->where('u.id = ?',$user_id);}

        
        
        $select_cursos = clone $select;
        $select_cursos = $select_cursos->where("gi.itemtype = 'course' ")
                                       ->order('gi.courseid','u.firstname','u.lastname');
                                       
        $select_disciplinas = $select->where("gi.itemtype = 'category' ")
                                     ->order('gi.courseid','gc.fullname','u.firstname','u.lastname');
        
        $cursos = $select_cursos->query()->fetchAll();
        $disciplinas = $select_disciplinas->query()->fetchAll();
        
        return $cursos;
    }

    
}
